package com.kura.uednews.db;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import com.kura.uedews.R;
import com.kura.uednews.model.Categories;
import com.kura.uednews.model.Contact;
import com.kura.uednews.model.ToDoItem;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;
import android.widget.Toast;

public class newsDB {
	// Database constants
	public final static String DB_NAME = "data.db";
	public final static int DB_VERSION = 8;

	/*
	 * 
	 * CATEGORIES TABLE CONSTANTS
	 * ------------------------------------------------
	 * --------------------------
	 * ---------------------------------------------------------------
	 */

	// Categories Table Constants
	public final static String CATEGORIES_TABLE = "categories";

	public static final String CATEGORIES_ID = "id";
	public static final int CATEGORIES_ID_COL = 0;

	public static final String CATEGORIES_NAME = "name";
	public static final int CATEGORIES_NAME_COL = 1;

	public static final String CATEGORIES_LINK = "link";
	public static final int CATEGORIES_LINK_COL = 2;

	public static final String CATEGORIES_PARENT = "parent";
	public static final int CATEGORIES_PARENT_COL = 3;

	public static final String CATEGORIES_SUBSCRIBE = "subscribe";
	public static final int CATEGORIES_SUBSCRIBE_COL = 4;

	// Create CATEGORIES table
	public static final String CREATE_CATEGORIES_TABLE = "CREATE TABLE categories ("
			+ " id int(11) NOT NULL,"
			+ " name varchar(250) NOT NULL,"
			+ " link varchar(500) NOT NULL,"
			+ " parent int(11),"
			+ " subscribe int(1)," + " PRIMARY KEY (id));";

	// Drop CATEGORIES table
	public final static String DROP_CATEGORIES_TABLE = "DROP TABLE IF EXISTS "
			+ CATEGORIES_TABLE;

	/*
	 * 
	 * ARTICLES TABLE CONSTANTS
	 * --------------------------------------------------
	 * ------------------------
	 * ---------------------------------------------------------------
	 */

	// Articles Table Constants
	public final static String ARTICLES_TABLE = "articles";

	public static final String ARTICLES_ID = "id";
	public static final int ARTICLES_ID_COL = 0;

	public static final String ARTICLES_DESCRIPTION = "description";
	public static final int ARTICLES_DESCRIPTION_COL = 1;

	public static final String ARTICLES_LINK = "link";
	public static final int ARTICLES_LINK_COL = 2;

	public static final String ARTICLES_CATEGORY = "cate";
	public static final int ARTICLES_CATEGORY_COL = 3;

	public static final String ARTICLES_IMAGE = "image";
	public static final int ARTICLES_IMAGE_COL = 4;

	// Create ARTICLES table
	public static final String CREATE_ARTICLES_TABLE = "CREATE TABLE articles ("
			+ " id int(11) NOT NULL,"
			+ " description varchar(250) NOT NULL,"
			+ " link varchar(500) NOT NULL,"
			+ " cate int(11),"
			+ " image varchar(250)," + " PRIMARY KEY (id));";

	// Drop ARTICLES table
	public final static String DROP_ARTICLES_TABLE = "DROP TABLE IF EXISTS "
			+ ARTICLES_TABLE;

	/*
	 * 
	 * FACULTY TABLE CONSTANTS
	 * --------------------------------------------------
	 * ------------------------
	 * ---------------------------------------------------------------
	 */
	public final static String FACULTY_TABLE = "faculty";

	public static final String FACULTY_ID = "id";
	public static final int FACULTY_ID_COL = 0;

	public static final String FACULTY_NAME = "name";
	public static final int FACULTY_NAME_COL = 1;

	// Create FACULTY table
	public static final String CREATE_FACULTY_TABLE = "CREATE TABLE faculty ("
			+ " id int(11) NOT NULL," + " name varchar(250) NOT NULL,"
			+ " PRIMARY KEY (id));";

	// Drop FACULTY table
	public static final String DROP_FACULTY_TABLE = "DROP TABLE IF EXISTS "
			+ FACULTY_TABLE;

	/*
	 * 
	 * CONTACTs TABLE CONSTANTS
	 * --------------------------------------------------
	 * ------------------------
	 * ---------------------------------------------------------------
	 */

	public final static String CONTACT_TABLE = "contact";

	public static final String CONTACT_ID = "id";
	public static final int CONTACT_ID_COL = 0;

	public static final String CONTACT_NAME = "name";
	public static final int CONTACT_NAME_COL = 1;

	public static final String CONTACT_EMAIL = "email";
	public static final int CONTACT_EMAIL_COL = 2;

	public static final String CONTACT_PHONE = "phone";
	public static final int CONTACT_PHONE_COL = 3;

	public static final String CONTACT_FACULTY = "faculty";
	public static final int CONTACT_FACULTY_COL = 4;

	// Create CONTACT table
	public static final String CREATE_CONTACT_TABLE = "CREATE TABLE contact ("
			+ " id int(11) NOT NULL," + " name varchar(250) NOT NULL,"
			+ " email varchar(250) NOT NULL," + " phone varchar(12) NOT NULL,"
			+ " faculty int(11) NOT NULL," + " PRIMARY KEY (id));";

	// Drop CONTACT table
	public static final String DROP_CONTACT_TABLE = "DROP TABLE IF EXISTS "
			+ CONTACT_TABLE;

	/*
	 * 
	 * TODOLIST TABLE CONSTANTS
	 * ------------------------------------------------------
	 * --------------------
	 * ---------------------------------------------------------------
	 */

	public static final String TODO_TABLE = "todo";
	
	public static final String TODO_ID = "id";
	public static final int TODO_ID_COL = 0;
	
	public static final String TODO_NAME = "name";
	public static final int TODO_NAME_COL = 1;
	
	public static final String TODO_NOTIFY = "notify";
	public static final int TODO_NOTIFY_COL = 2;
	
	public static final String TODO_date = "date";
	public static final int TODO_DATE_COL = 3;

	public static final String TODO_DONE = "done";
	public static final int TODO_DONE_COL = 4;
	
	// Create FACULTY table
		public static final String CREATE_TODO_TABLE = "CREATE TABLE todo ("
				+ " id int(11) NOT NULL," 
				+ " name varchar(250) NOT NULL,"
				+ " notify int(1) NOT NULL," 
				+ " date varchar(8) NOT NULL,"
				+ " done int(1) NOT NULL," 
				+ " PRIMARY KEY (id));";

		// Drop FACULTY table
		public static final String DROP_TODO_TABLE = "DROP TABLE IF EXISTS "
				+ TODO_TABLE;
	
	/*
	 * 
	 * DATABASE INTEGRATION
	 * ------------------------------------------------------
	 * --------------------
	 * ---------------------------------------------------------------
	 */

	private static class DBHelper extends SQLiteOpenHelper {
		private static final String DB_DIR = "/data/data/com.kura.uednews/databases/";
		private static String DB_PATH = DB_DIR + DB_NAME;
		private static String OLD_DB_PATH = DB_DIR + "old_" + DB_NAME;

		private final Context myContext;

		private boolean createDatabase = false;
		private boolean upgradeDatabase = false;

		public DBHelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, factory, version);
			myContext = context;
			DB_PATH = myContext.getDatabasePath(DB_NAME).getAbsolutePath();
			Log.i("abcd", myContext.getDatabasePath(DB_NAME).getAbsolutePath());
		}

		public DBHelper(Context context) {
			super(context, DB_NAME, null, context.getResources().getInteger(
					R.string.databaseVersion));
			myContext = context;
			// Get the path of the database that is based on the context.
			DB_PATH = myContext.getDatabasePath(DB_NAME).getAbsolutePath();
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			createDatabase = true;
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			Log.d("NewsRssReader", "Upgrading db from version " + oldVersion
					+ " to " + newVersion);
			upgradeDatabase = true;
		}

		@Override
		public void onOpen(SQLiteDatabase db) {
			super.onOpen(db);
		}

		public void initializeDataBase() {
			getWritableDatabase();

			if (createDatabase) {
				try {
					copyDataBase();
				} catch (IOException e) {
					throw new Error("Error copying database");
				}
			} else if (upgradeDatabase) {
				try {
					copyDataBase();
				} catch (IOException e) {
					throw new Error("Error copying database");
				}
			}

		}

		private void copyDataBase() throws IOException {
			close();
			InputStream myInput = myContext.getAssets().open(DB_NAME);
			OutputStream myOutput = new FileOutputStream(DB_PATH);
			FileHelper.copyFile(myInput, myOutput);
			getWritableDatabase().close();
		}
	}

	/* 
	 * 
	 * METHOD TO INTERACTION WITH DB
	 * ------------------------------------------------------
	 * --------------------
	 * ---------------------------------------------------------------
	 */
	

	// database and database helper objects
	private SQLiteDatabase db;
	private DBHelper dbHelper;

	// constructor 1
	public newsDB() {
		// TODO Auto-generated constructor stub
	}

	// constructor 2
	public newsDB(Context context) {
		dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
		dbHelper.initializeDataBase();
	}

	// private methods
	private void openReadableDB() {
		db = dbHelper.getReadableDatabase();
	}

	private void openWriteableDB() {
		db = dbHelper.getWritableDatabase();
	}

	private void closeDB() {
		if (db != null)
			db.close();
	}

	
	public ArrayList<Categories> getListAll() {
		ArrayList<Categories> lists = new ArrayList<Categories>();
		openReadableDB();
		Cursor cursor = db.query(CATEGORIES_TABLE, null, null, null, null,
				null, null);
		while (cursor.moveToNext()) {
			Categories category = new Categories();
			category.setId(cursor.getInt(CATEGORIES_ID_COL));
			category.setName(cursor.getString(CATEGORIES_NAME_COL));
			category.setLink(cursor.getString(CATEGORIES_LINK_COL));
			category.setParent(cursor.getInt(CATEGORIES_PARENT_COL));
			category.setSubscribe(cursor.getInt(CATEGORIES_SUBSCRIBE_COL));
			lists.add(category);
		}
		if (cursor != null)
			cursor.close();
		closeDB();

		return lists;
	}

	public ArrayList<Categories> getListAllRoot() {
		ArrayList<Categories> lists = new ArrayList<Categories>();
		openReadableDB();
		Cursor cursor = db.query(CATEGORIES_TABLE, null, "parent = -1",
				null, null, null, null);
		while (cursor.moveToNext()) {
			Categories category = new Categories();
			category.setId(cursor.getInt(CATEGORIES_ID_COL));
			category.setName(cursor.getString(CATEGORIES_NAME_COL));
			category.setLink(cursor.getString(CATEGORIES_LINK_COL));
			category.setParent(cursor.getInt(CATEGORIES_PARENT_COL));
			category.setSubscribe(cursor.getInt(CATEGORIES_SUBSCRIBE_COL));
			lists.add(category);
		}
		if (cursor != null)
			cursor.close();
		closeDB();

		return lists;
	}

	public ArrayList<Categories> getListAllByParent(int parent) {
		ArrayList<Categories> lists = new ArrayList<Categories>();
		openReadableDB();
		Cursor cursor = db.query(CATEGORIES_TABLE, null, "parent = '" + parent
				+ "'", null, null, null, null);
		while (cursor.moveToNext()) {
			Categories category = new Categories();
			category.setId(cursor.getInt(CATEGORIES_ID_COL));
			category.setName(cursor.getString(CATEGORIES_NAME_COL));
			category.setLink(cursor.getString(CATEGORIES_LINK_COL));
			category.setParent(cursor.getInt(CATEGORIES_PARENT_COL));
			category.setSubscribe(cursor.getInt(CATEGORIES_SUBSCRIBE_COL));
			lists.add(category);
		}
		if (cursor != null)
			cursor.close();
		closeDB();

		return lists;
	}

	public void setSubscribe(int id, int i) {
		openWriteableDB();
		db.execSQL("UPDATE categories SET subscribe=" + i + " WHERE id=" + id);
		closeDB();
	}

	public int getSubscribeById(int id) {
		int subScribe = 0;
		openReadableDB();
		Cursor cursor = db.query(CATEGORIES_TABLE, null, "id = '" + id + "'",
				null, null, null, null);

		while (cursor.moveToNext()) {
			subScribe = cursor.getInt(CATEGORIES_SUBSCRIBE_COL);
		}

		if (cursor != null) {
			cursor.close();
		}

		closeDB();

		return subScribe;
	}

	public String getLinkById(int id) {
		String link = "";
		openReadableDB();
		Cursor cursor = db.query(CATEGORIES_TABLE, null, "id = '" + id + "'",
				null, null, null, null);

		while (cursor.moveToNext()) {
			link = cursor.getString(CATEGORIES_LINK_COL);
		}

		if (cursor != null) {
			cursor.close();
		}

		closeDB();

		return link;
	}

	public void insertNews() {
		// db.insert(table, nullColumnHack, values);
	}

	public ArrayList<Categories> getListSubscribe() {
		ArrayList<Categories> lists = new ArrayList<Categories>();
		openReadableDB();
		Cursor cursor = db.query(CATEGORIES_TABLE, null, "subscribe = '1'", null, null, null, null);
		while (cursor.moveToNext()) {
			Categories category = new Categories();
			category.setId(cursor.getInt(CATEGORIES_ID_COL));
			category.setName(cursor.getString(CATEGORIES_NAME_COL));
			category.setLink(cursor.getString(CATEGORIES_LINK_COL));
			category.setParent(cursor.getInt(CATEGORIES_PARENT_COL));
			category.setSubscribe(cursor.getInt(CATEGORIES_SUBSCRIBE_COL));
			lists.add(category);
		}
		if (cursor != null)
			cursor.close();
		closeDB();

		return lists;
	}
	
	public ArrayList<ToDoItem> getListToDo(){
		ArrayList<ToDoItem> lists = new ArrayList<ToDoItem>();
		openReadableDB();
		Cursor cursor = db.query(TODO_TABLE, null, null, null, null, null, null);
		while (cursor.moveToNext()) {
			ToDoItem todo = new ToDoItem();
			todo.setId(cursor.getInt(TODO_ID_COL));
			todo.setName(cursor.getString(TODO_NAME_COL));
			todo.setNotify(cursor.getInt(TODO_NOTIFY_COL));
			todo.setDate(cursor.getString(TODO_DATE_COL));
			todo.setDone(cursor.getInt(TODO_DONE_COL));
			lists.add(todo);
		}
		if (cursor != null)
			cursor.close();
		closeDB();
		
		return lists;
	}
	
	public void addToDoItem(String name){
		openWriteableDB();
		String sql =
	            "INSERT INTO "+TODO_TABLE+" ("+TODO_NAME+","+TODO_DONE+") VALUES('"+name+"','0')" ;       
	                db.execSQL(sql);
	}
	
	public void setDone(String name){
		openWriteableDB();
		String sql =
	            "Update "+TODO_TABLE+" Set done = 1 Where name='"+name+"'" ;       
	                db.execSQL(sql);
	}
	
	public void setUndone(String name){
		openWriteableDB();
		String sql =
	            "Update "+TODO_TABLE+" Set done = 0 Where name='"+name+"'" ;       
	                db.execSQL(sql);
	}
	
	public ArrayList<Contact> getListContact(){
		ArrayList<Contact> lists = new ArrayList<Contact>();
		openReadableDB();
		Cursor cursor = db.query(CONTACT_TABLE, null, null, null, null, null, null);
		while (cursor.moveToNext()) {
			Contact c = new Contact();
			c.setName(cursor.getString(CONTACT_NAME_COL));
			c.setPhone(cursor.getString(CONTACT_PHONE_COL));
			lists.add(c);
		}
		if (cursor != null)
			cursor.close();
		closeDB();
		
		return lists;
	}
}
